Перейти к основному содержимому

3.08. Управление РСУБД

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Управление РСУБД​

Реляционные системы управления базами данных (РСУБД) — это программные комплексы, обеспечивающие физическое хранение, логическую организацию, целостность, безопасность и доступ к структурированным данным. Современная эксплуатация РСУБД выходит далеко за рамки простого «установил — запустил». Эффективное управление требует системного подхода, охватывающего подготовку, развёртывание, конфигурирование, администрирование, обеспечение отказоустойчивости и производительности. В отличие от встраиваемых СУБД (например, SQLite), управление серверными РСУБД предполагает наличие выделенной инфраструктуры, регулярного обслуживания и глубокого понимания внутренних механизмов системы.

Подготовка к установке​

Подготовительный этап определяет долгосрочную стабильность и безопасность системы. Ошибки, допущенные здесь, сложно исправить без остановки сервиса или миграции данных. Этап включает принятие архитектурных решений, расчёт ресурсов и подготовку окружения.

Выбор платформы​

Выбор конкретной РСУБД — стратегическое решение, основанное на совокупности факторов. Ни одна из систем не является «универсальной», и оптимальный выбор зависит от контекста: типа приложения, требований к отказоустойчивости, бюджета, кадрового состава и регуляторных ограничений.

Лицензирование. PostgreSQL распространяется по лицензии PostgreSQL License, близкой к MIT — разрешено свободное использование, модификация и коммерческая эксплуатация без ограничений. Oracle Database в стандартной поставке предоставляет бесплатную редакцию Express Edition (XE) с жёсткими ограничениями по объёму памяти и CPU; основные редакции (Standard, Enterprise) требуют платных лицензий, стоимость которых зависит от числа ядер процессора и типа поддержки. Microsoft SQL Server также имеет бесплатную редакцию Express, а платные редакции (Standard, Enterprise) лицензируются по числу ядер или на основе модели CAL (Client Access License). MySQL доступен в двух редакциях: Community Edition под лицензией GPL (свободное использование без поддержки) и Enterprise Edition — коммерческая версия с расширенными инструментами и технической поддержкой от Oracle.

Поддержка операционных систем. PostgreSQL официально поддерживает Linux, Windows, macOS, FreeBSD и другие Unix-подобные системы; развёртывание на Linux считается наиболее производительным и предпочтительным в промышленной эксплуатации. Oracle Database традиционно имеет наилучшую поддержку на Linux и Solaris, полноценный порт для Windows существует, но в некоторых сценариях (например, RAC на Windows) возможны ограничения. Microsoft SQL Server исторически работал только на Windows, однако начиная с версии 2017 поддерживает Linux (Red Hat, SUSE, Ubuntu), хотя некоторые компоненты (например, SSIS, SSRS, SSAS) остаются доступны только на Windows. MySQL одинаково хорошо работает на всех основных платформах, включая облачные и встраиваемые ОС.

Требования к «железу». PostgreSQL эффективно масштабируется как вертикально (увеличение ресурсов одного сервера), так и горизонтально (шардинг, репликация), но требует адекватного объёма оперативной памяти для буферизации и сортировки. Oracle Database, особенно в конфигурации RAC или при использовании In-Memory Option, предъявляет высокие требования к памяти и CPU, а также к стабильности дисковой подсистемы. Microsoft SQL Server Enterprise Edition может использовать до 12 TB ОЗУ и 240 логических процессоров, но даже Standard Edition требует значительных ресурсов при нагрузке. MySQL наиболее лёгок в базовой конфигурации, однако при включении InnoDB, кэшировании и репликации требования сближаются с PostgreSQL.

Сообщество и экосистема. PostgreSQL обладает крупным, активным, независимым сообществом разработчиков и пользователей; документация высокого качества, поддержка распространяется через коммерческие компании и форумы. Oracle и Microsoft обеспечивают официальную поддержку по контрактам, но сообщества пользователей, хоть и велики, в большей степени ориентированы на корпоративных клиентов. MySQL, несмотря на принадлежность Oracle, сохраняет сильное open-source сообщество, особенно вокруг Percona и MariaDB-форков.

Планирование ресурсов​

Ресурсное планирование — это расчёт, основанный на профиле нагрузки, объёме данных, требованиях к времени отклика и стратегии резервного копирования.

Выделение вычислительного окружения. Рекомендуется изолировать СУБД от других сервисов. Использование выделенного физического сервера обеспечивает максимальную предсказуемость производительности и упрощает диагностику. Виртуальная машина (ВМ) допустима при условии гарантии выделения ресурсов (CPU reservation, memory reservation) и низкой конкуренции за I/O. Контейнеризация (Docker, Podman) уместна для тестовых и разработческих сред, а также в микросервисных архитектурах, но требует особой осторожности в production: нельзя смешивать слои хранения контейнера и данных СУБД, необходимо обеспечить постоянное хранилище (volume) с гарантией durability и производительности, а также учитывать ограничения на shared memory и IPC.

Планирование дискового пространства. Объём необходимо рассчитывать с учётом роста, служебных файлов и операций обслуживания. Составляющие:

  • Пространство под операционную систему (минимум 20–30 ГБ для Linux, 40–60 ГБ для Windows).
  • Пространство под установку СУБД (от 1 до 5 ГБ в зависимости от компонентов).
  • Пространство под данные (основной объём; учитывается коэффициент роста, фрагментация, индексы — они могут занимать до 50–100% объёма таблиц).
  • Пространство под логи транзакций (WAL в PostgreSQL, Redo Logs в Oracle, Transaction Log в MS SQL, Binary Log в MySQL): критически важно расположить их на отдельном физическом диске или RAID-массиве (предпочтительно SSD с высокой устойчивостью к записи); объём должен покрывать не менее 24–48 часов интенсивной записи при штатной нагрузке.
  • Пространство под архивные логи (если включено архивирование WAL/Redo/Transaction Log).
  • Пространство под резервные копии: полные бэкапы хранятся отдельно от основного сервера, но при использовании инкрементальных или дифференциальных стратегий необходимо место для временного хранения и сбора цепочек восстановления.
  • Пространство под временные файлы (сортировка, хеширование, временные таблицы): может временно достигать значительных объёмов при сложных запросах.

Файловая система и монтирование. На Linux рекомендуются XFS или ext4. XFS предпочтителен для больших томов и высоконагруженных операций записи благодаря эффективной обработке больших файлов и журналированию метаданных. ext4 проще в диагностике и хорошо зарекомендовал себя в большинстве сценариев. Для обоих файловых систем при монтировании рекомендуется использовать опцию noatime (или relatime), исключающую запись времени последнего доступа к файлу — это снижает нагрузку на диск без потери функциональности. На Windows используется NTFS с включённым журналированием метаданных (по умолчанию). Важно отключить антивирусную проверку в реальном времени для каталогов данных и логов СУБД — это может вызывать задержки и блокировки.

Пользователь операционной системы. Служба СУБД никогда не должна запускаться от имени суперпользователя (root в Linux, LocalSystem/NT AUTHORITY\SYSTEM в Windows). Создаётся отдельный непривилегированный пользователь (например, postgres, oracle, mssql, mysql), которому выдаются права только на необходимые каталоги (данные, логи, бинарники). Это соответствует принципу минимальных привилегий и ограничивает потенциальный ущерб при компрометации процесса СУБД.

Безопасность до установки​

Безопасность должна закладываться на этапе планирования, а не добавляться после инцидента.

Настройка брандмауэра. По умолчанию все порты закрыты. Открываются только те, которые действительно необходимы:

  • PostgreSQL: 5432 (основной порт; может быть изменён);
  • Oracle Database: 1521 (порт по умолчанию для Listener; может быть настроен на другой);
  • Microsoft SQL Server: 1433 (порт по умолчанию для именованного экземпляра MSSQLSERVER); для именованных экземпляров используется SQL Server Browser на UDP-порту 1434;
  • MySQL: 3306 (порт по умолчанию).

При этом правила брандмауэра должны ограничивать доступ по IP-адресам: разрешать соединения только с серверов приложений, административных хостов и систем мониторинга. Доступ «со всего интернета» недопустим.

Сетевая архитектура. Рекомендуется выделять отдельную VLAN или подсеть для:

  • Взаимодействия между узлами кластера (репликация, синхронизация состояния);
  • Доступа систем резервного копирования;
  • Административного доступа (отделённого от трафика приложений).

Для критически важных систем применяется принцип «зона доверия»: сервер БД находится в защищённой внутренней сети, к которой нет прямого доступа из внешней среды; доступ к данным осуществляется только через промежуточные сервисы (API-шлюзы, приложения).

Стратегия управления учётными данными. На этапе подготовки определяется:

  • Где и как хранятся пароли суперпользователей (например, в защищённом менеджере паролей, недоступном для автоматизированных систем);
  • Как будут создаваться и ротироваться пароли для сервисных аккаунтов;
  • Будет ли использоваться централизованная аутентификация (LDAP, Active Directory, Kerberos);
  • Какие требования к сложности паролей и сроку их действия.

Эти правила фиксируются в регламенте эксплуатации, а не оставляются на усмотрение администратора.

Установка СУБД​

Установка — это не просто запуск инсталлятора. Это процесс приведения программного комплекса в состояние, пригодное для дальнейшей настройки и эксплуатации. Ошибки на этом этапе (например, использование дефолтных путей в /tmp, запуск от root, пропуск инициализации) могут сделать дальнейшую эксплуатацию затруднительной или небезопасной.

PostgreSQL​

Основной способ установки PostgreSQL на Linux — использование официальных репозиториев, поддерживаемых сообществом (например, https://www.postgresql.org/download/linux/). Это гарантирует получение актуальных версий и автоматическое разрешение зависимостей. Установка пакета postgresql-server (или аналогичного, в зависимости от дистрибутива) размещает бинарные файлы, но не инициализирует кластер — это ключевой момент, отличающий PostgreSQL от многих других СУБД.

Инициализация кластера — создание структуры каталогов данных, системных таблиц, шаблонных баз (template0, template1) и учётной записи суперпользователя postgres. Выполняется вручную командой initdb, обычно через скрипт-обёртку, поставляемый с пакетом (например, postgresql-setup initdb в RHEL/CentOS или /usr/lib/postgresql/*/bin/initdb в Ubuntu). Важно явно указать параметры кодировки (--encoding=UTF8), локали (--locale=en_US.UTF-8) и каталог данных (-D /var/lib/pgsql/data), чтобы избежать неожиданного поведения в будущем. Без инициализации служба PostgreSQL не запустится.

После инициализации служба запускается стандартными средствами ОС: systemctl start postgresql (Linux) или через «Службы» в Windows. При первом запуске автоматически создаётся системная база postgres и пользователь postgres с аутентификацией по умолчанию через peer (Linux) или без пароля (локально, Windows). Это состояние требует немедленной настройки безопасности.

Oracle Database​

Установка Oracle Database — многоэтапный процесс, разделённый на два логических компонента: Database Software и Database Instance.

Установка Database Software выполняется через Oracle Universal Installer (OUI) — графический или консольный инсталлятор. OUI копирует бинарные файлы в указанный ORACLE_HOME, настраивает переменные окружения (ORACLE_BASE, ORACLE_HOME, PATH) и создаёт базовую структуру каталогов. На этом этапе СУБД ещё не функционирует как сервер — это лишь «коробка инструментов».

Создание базы данных — отдельная операция. Стандартный способ — использование Database Configuration Assistant (DBCA), который может запускаться как часть OUI или отдельно. DBCA предоставляет пошаговый мастер: выбор типа БД (одноузловая, RAC), параметры хранения (ASM, файловая система), настройка памяти (SGA, PGA), включение опций (Enterprise Manager, архивный режим), создание учётных записей (SYS, SYSTEM). DBCA генерирует и выполняет все необходимые SQL-скрипты, создаёт файлы данных, журналы и параметрические файлы (spfile.ora или init.ora). Альтернативно, опытные администраторы могут создать экземпляр вручную через SQL*Plus (CREATE DATABASE), но это требует глубокого знания структуры Oracle и крайне редко применяется в production.

Запуск и остановка экземпляра управляется через SQL*Plus (CONNECT / AS SYSDBA, затем STARTUP / SHUTDOWN) или, в конфигурациях RAC, через srvctl.

Microsoft SQL Server​

Установка SQL Server начинается с запуска setup.exe. Инсталлятор предлагает выбрать компоненты: основной — Database Engine Services, но также могут потребоваться SQL Server Replication, Full-Text and Semantic Extractions, Machine Learning Services и другие. Ключевой выбор — тип экземпляра:

  • По умолчанию (MSSQLSERVER) — слушает стандартный порт 1433;
  • Именованный (например, PROD) — требует указания имени при подключении и, по умолчанию, использует динамические порты (назначаются SQL Server Browser).

Для production рекомендуется использовать именованные экземпляры, даже если на сервере один экземпляр — это облегчает будущую миграцию и изоляцию.

На этапе настройки учётных записей необходимо создать логин для встроенной роли sysadmin. По умолчанию SQL Server может использовать аутентификацию Windows (Integrated Security), но для гибкости часто включают смешанный режим (Windows + SQL-аутентификация) и задают сложный пароль для учётной записи sa.

После завершения установки служба SQL Server (MSSQLSERVER) автоматически запускается. Её состояние и параметры (включая привязку к портам) контролируются через SQL Server Configuration Manager — специализированный инструмент, входящий в поставку.

MySQL​

Установка MySQL на Linux обычно выполняется через пакетный менеджер (apt install mysql-server в Ubuntu, yum install mysql-community-server в RHEL). На Windows используется GUI-инсталлятор от Oracle. В обоих случаях после установки пакета служба mysqld запускается автоматически.

Первый запуск инициализирует системные таблицы в каталоге данных (обычно /var/lib/mysql), генерирует временный пароль для пользователя root (в логах, например, /var/log/mysqld.log) и создаёт базовые привилегии.

Критически важный шаг — запуск скрипта mysql_secure_installation. Он предлагает:

  • Сменить временный пароль root;
  • Удалить анонимных пользователей;
  • Запретить удалённый вход под root;
  • Удалить тестовую базу test;
  • Обновить привилегии.

Пропуск этого шага оставляет СУБД в уязвимом состоянии. После его выполнения MySQL готова к дальнейшей настройке.

MySQL Workbench — отдельный графический клиент и инструмент администрирования, устанавливаемый дополнительно. Он не является частью сервера и не влияет на его работу.

Конфигурация​

Конфигурация — это процесс настройки поведения СУБД под конкретные требования приложения, аппаратной платформы и политик безопасности. Большинство параметров можно изменить «на лету» (без перезапуска), но некоторые требуют restart — это обязательно учитывается при планировании изменений.

Ключевые конфигурационные файлы​

Каждая СУБД имеет свои файлы настройки, но их назначение универсально: управление ресурсами, сетевым взаимодействием, журналированием и безопасностью.

PostgreSQL использует два основных файла:

  • postgresql.conf — содержит сотни параметров: память, параллелизм, журналы, локали, пути. Изменения вступают в силу после pg_ctl reload или перезапуска, в зависимости от параметра (postmaster vs sighup).
  • pg_hba.conf — Host-Based Authentication: таблица правил, определяющая, как и кому разрешено подключаться. Порядок правил важен: первое совпадение применяется. Формат: тип_подключения база пользователь адрес_сети метод_аутентификации.

Oracle Database полагается на параметрические файлы:

  • spfile.ora (бинарный, предпочтителен) или init.ora (текстовый) — хранят параметры экземпляра (SGA_TARGET, PROCESSES, DB_NAME и др.). Изменения могут применяться динамически (ALTER SYSTEM SET ... SCOPE=BOTH) или требовать restart (SCOPE=SPFILE).
  • listener.ora — конфигурация Listener’а, процесса, принимающего входящие соединения и передающего их в экземпляр.
  • tnsnames.ora — клиентский файл, содержащий псевдонимы для подключения (упрощает указание host:port/service_name).

Microsoft SQL Server исторически использовал файл sqlservr.ini, но сейчас основная конфигурация выполняется через:

  • SQL Server Management Studio (SSMS) — графический интерфейс с разделом «Свойства сервера»;
  • Системные хранимые процедуры, например sp_configure, которые изменяют параметры в памяти (RECONFIGURE применяет без restart) или требуют restart.

MySQL использует my.cnf (Linux/macOS) или my.ini (Windows). Файл может быть фрагментирован: глобальные настройки в /etc/my.cnf, пользовательские — в ~/.my.cnf. Параметры группируются по секциям: [mysqld] — для сервера, [client] — для клиентов.

Критически важные параметры​

Некоторые настройки оказывают фундаментальное влияние на стабильность и безопасность.

Слушающие адреса. По умолчанию многие СУБД слушают только localhost, что блокирует удалённые подключения.

  • В PostgreSQL: listen_addresses = 'localhost' → listen_addresses = '*' (или конкретный IP).
  • В MySQL: bind-address = 127.0.0.1 → bind-address = 0.0.0.0.
  • В Oracle: listener.ora должен содержать HOST = 0.0.0.0 или конкретный IP.
  • В MS SQL: в Configuration Manager включаются протоколы (TCP/IP) и указывается порт (статический вместо динамического).

Изменение этих параметров без настройки pg_hba.conf (Pg), GRANT (MySQL), logins (MS SQL) или sqlnet.ora (Oracle) делает СУБД доступной, но не аутентифицирует клиентов — это создаёт угрозу.

Память. Неправильное распределение памяти — частая причина деградации производительности или сбоев.

  • PostgreSQL: shared_buffers (буфер кэша ядра, ~25% ОЗУ), work_mem (память на операцию сортировки/хеширования, на запрос), maintenance_work_mem (для VACUUM, CREATE INDEX).
  • Oracle: SGA_TARGET/SGA_MAX_SIZE (разделяемая память: кэш буферов, shared pool), PGA_AGGREGATE_TARGET (приватная память процессов).
  • MS SQL: max server memory (верхний лимит ОЗУ, используемой буферным пулом; обязательно ограничивать, чтобы не «съесть» всю память ОС).
  • MySQL: innodb_buffer_pool_size (главный кэш InnoDB, до 70–80% ОЗУ при dedicated-сервере), key_buffer_size (для MyISAM, если используется).

Настройка «по максимуму» без учёта других процессов на сервере приводит к своппингу и падению производительности.

Журналирование. Обеспечивает восстанавливаемость после сбоев.

  • PostgreSQL: wal_level = replica (минимум для репликации и PITR), archive_mode = on + archive_command (копирование WAL-файлов).
  • Oracle: переключение в ARCHIVELOG режим (ALTER DATABASE ARCHIVELOG) — обязательное условие для горячих бэкапов и PITR.
  • MS SQL: выбор Recovery Model: FULL (полное журналирование, необходим для log backup и PITR), SIMPLE (автоматическая усечка лога, PITR невозможен).
  • MySQL: log_bin = ON (включение бинарных логов), binlog_format = ROW (рекомендуется для репликации и PITR).

Без включённого архивирования журналов транзакций невозможно гарантированное восстановление на произвольный момент времени.

Автоматическое обслуживание. Поддержание «здоровья» базы данных — регулярная задача.

  • В PostgreSQL autovacuum автоматически выполняет VACUUM (освобождение места от удалённых строк) и ANALYZE (обновление статистики для оптимизатора). Отключать его нельзя; регулируются параметры агрессивности (autovacuum_vacuum_scale_factor).
  • В Oracle автоматические задания (DBMS_SCHEDULER) управляют сбором статистики (GATHER_STATS_JOB), очисткой SYSAUX.
  • В MS SQL SQL Server Agent запускает планы обслуживания: UPDATE STATISTICS, REBUILD INDEX, CHECKDB.
  • В MySQL innodb_file_per_table = ON (по умолчанию с 5.6) позволяет эффективно освобождать место при удалении таблиц; OPTIMIZE TABLE физически дефрагментирует, но блокирует таблицу.

Работа с терминальными клиентами​

Терминальные утилиты — основной инструмент администрирования. Они незаменимы при отсутствии графического интерфейса (сервер без GUI), в процессах автоматизации (скрипты, CI/CD), при диагностике (например, когда GUI «висит» из-за блокировки) и для точного контроля над выполнением команд.

PostgreSQL: psql
Это не просто «клиент для SQL». Это полноценная среда с поддержкой:

  • Мета-команд (начинаются с \): \l — список БД, \c dbname — подключение к БД, \d tablename — описание таблицы, \du — список ролей, \x — расширенный вывод, \timing — включение отображения времени выполнения.
  • Переменных (\set var 'value'), условного выполнения (\if), редактирования запросов.
  • Режима непосредственного выполнения (psql -c "SELECT 1"), что позволяет интегрировать SQL в shell-скрипты.

psql поддерживает .psqlrc — файл инициализации, где можно настроить prompt, подключить расширения, задать параметры подключения.

Oracle: sqlplus и sqlcl
sqlplus — классический клиент, работающий в текстовом режиме. Он позволяет:

  • Подключаться как к локальному экземпляру (/ AS SYSDBA), так и удалённо (user/pass@//host:port/service);
  • Выполнять SQL и анонимные блоки PL/SQL;
  • Управлять форматом вывода (SET LINESIZE, SET PAGESIZE, COLUMN ... FORMAT);
  • Выполнять скрипты (@script.sql).

sqlcl (SQL Developer Command Line) — современная замена, написанная на Java, с поддержкой автодополнения, цветного синтаксиса, JavaScript-скриптов и REST-вызовов. Но sqlplus остаётся стандартом де-факто в скриптах и документации.

Microsoft SQL Server: sqlcmd
Утилита командной строки, поддерживающая:

  • Подключение через Windows или SQL-аутентификацию;
  • Выполнение одиночных команд (sqlcmd -Q "SELECT @@VERSION");
  • Запуск скриптов из файла (sqlcmd -i script.sql);
  • Использование переменных (:setvar name value);
  • Управление соединением (:connect server\instance).

Основное применение — автоматизация развёртывания и обслуживания.

MySQL: mysql
Интерактивный клиент с поддержкой:

  • Истории команд, автодополнения;
  • Выполнения SQL-скриптов (mysql < dump.sql);
  • Вывода в различные форматы (--table, --batch, --xml);
  • Настройки через .my.cnf в домашнем каталоге.

Использование терминальных клиентов формирует у администратора глубокое понимание протокола взаимодействия и структуры метаданных, что невозможно при работе только через GUI.

Подключение и настройка доступа​

Настройка доступа — баланс между удобством и безопасностью. Частая ошибка — разрешить доступ «для всех», а потом пытаться «закрыть дыры».

Локальное подключение​

По умолчанию все СУБД позволяют подключаться локально с минимальными проверками:

  • PostgreSQL использует peer (Linux) или ident (Windows) — аутентификация по имени ОС-пользователя. Пользователь postgres ОС может подключиться без пароля к БД postgres.
  • Oracle позволяет подключиться как / AS SYSDBA локально через IPC, если пользователь входит в группу dba.
  • MS SQL разрешает Windows-аутентификацию для членов локальной группы Administrators.
  • MySQL в некоторых дистрибутивах настраивает auth_socket для root@localhost, позволяя вход без пароля через Unix-сокет.

Это удобно для первоначальной настройки, но такие методы должны быть отключены или ограничены после создания сервисных учётных записей.

Настройка удалённого подключения​

Удалённый доступ требует явного разрешения на двух уровнях: сеть и аутентификация.

PostgreSQL:

  1. В postgresql.conf: listen_addresses = 'IP_сервера' (не * без необходимости);
  2. В pg_hba.conf: добавить правило, например:
    host app_db app_user 192.168.1.100/32 scram-sha-256 —
    разрешает пользователю app_user подключаться к БД app_db только с IP 192.168.1.100, используя современный метод хеширования пароля.

Oracle:

  1. В listener.ora: убедиться, что HOST не localhost;
  2. Перезапустить Listener (lsnrctl reload);
  3. На клиенте создать запись в tnsnames.ora:
    APPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = appdb))).

MS SQL:

  1. В SQL Server Configuration Manager → Сетевые настройки SQL Server → Протоколы → включить TCP/IP;
  2. В свойствах TCP/IP указать статический порт (например, 1433);
  3. В брандмауэре открыть этот порт;
  4. В SSMS → Свойства сервера → Безопасность → разрешить SQL Server и Windows-проверку подлинности (если нужно).

MySQL:

  1. В my.cnf: bind-address = 0.0.0.0 (осторожно!) или конкретный IP;
  2. Перезапустить mysqld;
  3. В MySQL выполнить:
    CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'pass';
    GRANT ... ON app_db.* TO 'app_user'@'192.168.1.100';

Обратите внимание: в MySQL 'app_user'@'%' разрешает доступ с любого хоста, что крайне нежелательно в production.

Инструменты для проверки​

Перед запуском приложения необходимо убедиться, что соединение возможно:

  • telnet db-server 5432 — проверка открыт ли порт (если telnet установлен);
  • nc -zv db-server 1433 — то же, через netcat;
  • psql -h db-server -U app_user -d app_db — попытка аутентифицированного подключения;
  • sqlplus app_user/pass@//db-server:1521/appdb — для Oracle.

Отрицательный результат позволяет выявить проблему на сетевом или аутентификационном уровне до подключения приложения.

Создание БД и пользователей​

Создание базы данных и учётных записей — начало жизненного цикла, подчиняющегося принципам минимизации привилегий и разделения обязанностей. Цель — предоставить приложению ровно те права, которые необходимы для выполнения его функций, и ничего более.

Создание базы данных​

С технической точки зрения, база данных — это логическая группа объектов (таблиц, индексов, представлений), изолированная от других баз на уровне метаданных и (часто) файловой системы. Физическое размещение зависит от СУБД.

PostgreSQL использует концепцию кластера — совокупности баз данных, управляемых одним экземпляром сервера. Команда CREATE DATABASE app_db OWNER app_user; создаёт новую базу, копируя содержимое шаблона template1. Владелец (OWNER) получает полные права на все объекты в этой базе и может передавать их другим. Важно: база не изолирована на уровне ОС-процессов; изоляция обеспечивается только средствами СУБД.

Oracle Database традиционно не разделяет понятия «база данных» и «экземпляр». Физическая БД создаётся один раз (через DBCA или вручную), а логическая изоляция достигается через схемы (schemas), которые привязаны к пользователям. Команда CREATE USER app_user IDENTIFIED BY password; автоматически создаёт схему APP_USER, и все объекты, созданные этим пользователем, принадлежат этой схеме. Таким образом, «создание БД» в Oracle — это создание пользователя-владельца схемы.

Microsoft SQL Server предоставляет полную логическую изоляцию: CREATE DATABASE AppDB; создаёт отдельный файл данных и лога по умолчанию, изолированный на уровне файловой системы. Внутри БД создаются пользователи, привязанные к логинам на уровне сервера: CREATE LOGIN app_user WITH PASSWORD = '...'; CREATE USER app_user FOR LOGIN app_user;. Это двухуровневая модель: логин управляет доступом к серверу, пользователь — к конкретной базе.

MySQL сближает понятия «база данных» и «схема»: CREATE DATABASE app_db; и CREATE SCHEMA app_db; синонимичны. Все объекты создаются внутри этой базы/схемы. Пользователи определяются с привязкой к хосту: CREATE USER 'app_user'@'app-host' ...;, что усиливает безопасность за счёт привязки к источнику соединения.

Создание пользователей и ролей​

Учётные записи создаются с учётом их назначения: приложение, администратор, аналитик, оператор бэкапа. Для каждой роли определяется минимальный набор привилегий.

PostgreSQL различает роли и пользователи: CREATE USER — синоним CREATE ROLE ... LOGIN. Предпочтительно использовать CREATE ROLE, явно указывая LOGIN, SUPERUSER, CREATEDB и т.д. Для приложений создаются роли без SUPERUSER, CREATEDB, REPLICATION. Роли могут быть членами других ролей (GRANT role1 TO role2), что упрощает управление.

Oracle использует CREATE USER, после чего необходимо назначить привилегии. Системные привилегии (CREATE SESSION, CREATE TABLE) и объектные (SELECT ON schema.table) выдаются отдельно. Роли (CREATE ROLE app_role; GRANT SELECT ANY TABLE TO app_role; GRANT app_role TO app_user;) — основной механизм группировки прав.

MS SQL разделяет логины (серверный уровень) и пользователей (уровень базы). Привилегии назначаются через роли базы данных (db_datareader, db_datawriter) или напрямую (GRANT SELECT TO app_user). Важно: не следует добавлять прикладных пользователей в серверные роли (sysadmin, securityadmin).

MySQL создаёт пользователя и сразу может назначить привилегии: GRANT SELECT, INSERT ON app_db.* TO 'app_user'@'app-host';. Привилегии могут быть глобальными (*.*), на уровне базы (app_db.*) или таблицы (app_db.table1). Привязка к хосту ('app_user'@'192.168.1.%') позволяет гибко управлять доступом.

Назначение привилегий​

Принцип наименьших привилегий означает:

  • Приложению не нужны права DROP TABLE, ALTER PROCEDURE, CREATE USER;
  • Приложению не нужен доступ к системным таблицам (pg_catalog, information_schema, sys, dba_*);
  • Приложению не нужен доступ на запись в таблицы, используемые только для чтения (справочники, конфигурации).

В PostgreSQL типичный набор для веб-приложения:

GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
-- Если используется отдельная схема:
-- GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
-- Для будущих таблиц:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

В Oracle:

GRANT CREATE SESSION TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON app_schema.orders TO app_user;
GRANT SELECT ON app_schema.products TO app_user;  -- только чтение

В MS SQL:

USE AppDB;
CREATE USER app_user FOR LOGIN app_user;
-- Использование предопределённых ролей:
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
-- Или точечные права:
GRANT EXECUTE ON SCHEMA::dbo TO app_user;
DENY DELETE ON SCHEMA::config TO app_user;  -- явный запрет

В MySQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.orders TO 'app_user'@'app-host';
GRANT SELECT ON app_db.products TO 'app_user'@'app-host';
FLUSH PRIVILEGES;  -- требуется в старых версиях, сейчас часто не нужно

Запрет использования суперпользователей для приложений​

Использование учётных записей postgres, sa, root, SYS в коде приложения — грубейшая ошибка. Последствия:

  • Любая уязвимость в приложении даёт атакующему полный контроль над СУБД;
  • Отсутствие аудита: невозможно определить, какое приложение выполнило опасную операцию;
  • Невозможность отзыва прав без остановки всех сервисов.

Решение — строгая политика: суперпользовательские учётные записи используются только для первоначальной настройки и аварийного восстановления. Доступ к ним ограничен по IP, защищён многофакторной аутентификацией и фиксируется в журнале.

Администрирование и управление доступом​

Администрирование — это непрерывный цикл: мониторинг → диагностика → коррекция → проверка. Управление доступом — его неотъемлемая часть, обеспечивающая конфиденциальность, целостность и доступность данных.

Аутентификация и авторизация: разграничение понятий​

Аутентификация (AuthN) — процесс подтверждения личности субъекта: «Кто вы?»
Авторизация (AuthZ) — процесс проверки прав субъекта на выполнение операции: «Можете ли вы это сделать?»

Непонимание этой разницы приводит к ошибкам: например, настройка сложного LDAP-аутентификационного провайдера, но выдача всем вошедшим роли sysadmin.

Методы аутентификации​

Каждая СУБД поддерживает несколько методов, выбор зависит от требований безопасности и инфраструктуры.

Парольная аутентификация — базовый метод. Важно использовать криптостойкие алгоритмы:

  • PostgreSQL: scram-sha-256 (рекомендуется с версии 10), md5 (устаревший, уязвим к атакам offline);
  • Oracle: хеши хранятся в USER$, используется модифицированный SHA-1; с версии 12c поддерживается SHA2 для паролей;
  • MS SQL: хеши хранятся в sys.sql_logins, алгоритм зависит от версии (SHA-2 с 2012 SP1);
  • MySQL: caching_sha2_password (по умолчанию с 8.0), mysql_native_password (совместимость).

Сертификаты SSL/TLS используются для:

  • Шифрования трафика (защита от прослушивания);
  • Аутентификации клиента по сертификату (вместо пароля).
    В PostgreSQL: clientcert=verify-full в pg_hba.conf, sslmode=verify-full в строке подключения.
    В Oracle: настройка Wallet и SSL_SERVER_CERT_DN.
    В MS SQL: включение Force Encryption и настройка сертификата в Configuration Manager.
    В MySQL: REQUIRE X509 при создании пользователя.

Аутентификация ОС — делегирование проверки подлинности операционной системе:

  • PostgreSQL: peer (Unix-сокеты), ident (TCP, устаревший), ldap, pam;
  • MS SQL: Integrated Security=SSPI — использует Kerberos/NTLM;
  • MySQL: auth_socket (локально, по имени пользователя ОС).

Внешние сервисы:

  • LDAP/Kerberos: централизованное управление учётными записями (поддержка в PostgreSQL через pg_ldap, в Oracle через Enterprise User Security, в MS SQL через Windows Auth + AD);
  • PAM (Pluggable Authentication Modules) — гибкий фреймворк для Linux, позволяющий подключать произвольные модули (например, двухфакторную аутентификацию).

Управление привилегиями (авторизация)​

Привилегии делятся на:

  • Системные — действия на уровне сервера: CREATE DATABASE, CREATE ROLE, SHUTDOWN;
  • Объектные — действия над конкретными объектами: SELECT, INSERT, EXECUTE, REFERENCES.

Роли — механизм группировки привилегий. Вместо назначения прав десяткам пользователей, права назначаются роли, а пользователи добавляются в роль. Это упрощает управление и снижает риск ошибок. Например, роль analyst может включать SELECT на все таблицы, но не INSERT.

Row-Level Security (RLS) — политики, ограничивающие доступ на уровне строк. Полезно для мультиарендных приложений или разделения данных по отделам.

  • PostgreSQL: CREATE POLICY, ENABLE ROW LEVEL SECURITY ON table;
  • Oracle: Virtual Private Database (VPD), DBMS_RLS;
  • MS SQL: CREATE SECURITY POLICY ... ADD FILTER PREDICATE;
  • MySQL: нет встроенной поддержки (требуется реализация на уровне приложения или через представления).

Мониторинг​

Мониторинг — основа проактивного администрирования.

Активные сеансы показывают, что происходит сейчас:

  • PostgreSQL: SELECT * FROM pg_stat_activity; — pid, пользователь, база, запрос, состояние (active, idle), время начала.
  • Oracle: SELECT * FROM v$session WHERE type != 'BACKGROUND'; — sid, serial#, program, status, wait_event.
  • MS SQL: SELECT * FROM sys.dm_exec_sessions; или sp_who2 — session_id, login_name, status, cpu_time, reads.
  • MySQL: SHOW PROCESSLIST; или SELECT * FROM information_schema.processlist;.

Анализ позволяет выявить «висящие» транзакции, долгие запросы, аномальное число соединений.

Блокировки — частая причина деградации производительности:

  • PostgreSQL: pg_locks, pg_blocking_pids();
  • Oracle: v$lock, v$session_wait;
  • MS SQL: sys.dm_tran_locks, sys.dm_os_waiting_tasks;
  • MySQL: information_schema.innodb_trx, sys.innodb_lock_waits.

Журналы ошибок — первое место для диагностики сбоев. Расположение:

  • PostgreSQL: log_directory в postgresql.conf;
  • Oracle: background_dump_dest, user_dump_dest;
  • MS SQL: «Журналы SQL Server» в Configuration Manager или ERRORLOG в каталоге LOG;
  • MySQL: log_error в my.cnf.

Обслуживание​

Регулярное обслуживание предотвращает накопление технического долга.

PostgreSQL:

  • VACUUM: освобождает место от «мёртвых» строк (без FULL — онлайн, без блокировки);
  • VACUUM FULL: дефрагментирует, но блокирует таблицу (используется редко);
  • ANALYZE: обновляет статистику для оптимизатора;
  • REINDEX: перестраивает индексы при фрагментации.

Автоматизировано через autovacuum.

Oracle:

  • DBMS_STATS.GATHER_SCHEMA_STATS: сбор статистики;
  • ALTER INDEX ... REBUILD: перестроение индекса;
  • ALTER TABLE ... MOVE: дефрагментация таблицы.

MS SQL:

  • UPDATE STATISTICS: обновление статистики;
  • ALTER INDEX ... REORGANIZE/REBUILD: реорганизация или перестроение;
  • DBCC CHECKDB: проверка целостности.

MySQL:

  • ANALYZE TABLE: обновление статистики;
  • OPTIMIZE TABLE: эквивалент ALTER TABLE ... ENGINE=InnoDB — дефрагментация (блокирует таблицу).

Управление пользователями и аудит​

Смена паролей должна быть регламентирована: ALTER USER ... IDENTIFIED BY ... (Oracle), ALTER ROLE ... PASSWORD ... (PostgreSQL), ALTER LOGIN ... WITH PASSWORD ... (MS SQL), ALTER USER ... IDENTIFIED BY ... (MySQL).

Отзыв привилегий и удаление пользователей — операции с последствиями: проверяется, не используются ли права в других местах (например, в DEFINER у представлений или процедур).

Аудит — фиксация критических действий:

  • PostgreSQL: pgAudit (расширение), log_statement = 'ddl';
  • Oracle: AUDIT CREATE TABLE, DROP USER;;
  • MS SQL: SQL Server Audit (на уровне сервера или базы);
  • MySQL: Enterprise Audit Plugin или general_log (не для production).

Аудит должен записываться внешним хранилищем (отдельный сервер, SIEM), чтобы его нельзя было удалить локально.

Резервное копирование​

Резервное копирование — обязательный процесс, обеспечивающий выполнение требований по доступности и целостности. Отказ от бэкапа эквивалентен отказу от ответственности за данные.

Что такое резервное копирование и зачем оно нужно?​

Основные угрозы, от которых защищает бэкап:

  • Аппаратный сбой (отказ диска, контроллера, сервера);
  • Человеческая ошибка (удаление таблицы, DROP DATABASE, некорректный UPDATE);
  • Программный сбой (ошибка в коде приложения, баг в СУБД);
  • Кибератака (ransomware, SQL-инъекция с повреждением данных).

Резервная копия — единственный способ восстановить данные в целостном, согласованном состоянии после таких событий.

Основные типы резервного копирования​

Логическое копирование​

Экспорт данных в виде SQL-скриптов или структурированных текстовых файлов.

  • PostgreSQL: pg_dump (одна БД), pg_dumpall (все БД + глобальные объекты).
    Плюсы: переносимость между версиями и ОС, возможность фильтрации (--table, --schema), сжатие.
    Минусы: медленно на больших объёмах, блокировка таблиц при --lock-wait-timeout, отсутствие информации о физической структуре.

  • Oracle: expdp (Data Pump Export) — параллельный, эффективный экспорт в двоичный формат .dmp.
    Плюсы: гибкость (CONTENT=DATA_ONLY), поддержка сетевого экспорта.
    Минусы: требует места на диске, не поддерживает PITR напрямую.

  • MS SQL: bcp (Bulk Copy Program) или SSIS для экспорта таблиц в файлы.
    Примечание: BACKUP DATABASE ... TO DISK создаёт физический бэкап, несмотря на расширение .bak.

  • MySQL: mysqldump — генерирует SQL-скрипты CREATE и INSERT.
    Плюсы: простота, совместимость.
    Минусы: однопоточность (до 8.0), блокировка при --single-transaction не полная.

Физическое копирование​

Копирование файлов данных на уровне файловой системы или бинарных образов.

  • PostgreSQL: pg_basebackup (копирует весь кластер, включая WAL), pg_probackup (инкрементальные бэкапы), снимки LVM/ZFS.
    Плюсы: скорость, точное восстановление состояния.
    Минусы: привязка к версии и ОС, необходимость остановки или использования pg_start_backup() / pg_stop_backup().

  • Oracle: RMAN (Recovery Manager) — стандарт де-факто. Работает на уровне блоков, поддерживает инкрементальные бэкапы, сжатие, шифрование, интеграцию с ARCHIVELOG.
    Плюсы: надёжность, производительность, встроенная проверка целостности.
    Минусы: сложность настройки, требует обучения.

  • MS SQL: BACKUP DATABASE ... TO DISK — создаёт бинарный файл, содержащий данные и журнал транзакций на момент завершения бэкапа.
    Плюсы: интеграция с Recovery Model, поддержка сжатия и шифрования.
    Минусы: большой размер, зависит от версии.

  • MySQL: Percona XtraBackup — горячее копирование InnoDB без блокировки. Файловая копия возможна только при остановке (FLUSH TABLES WITH READ LOCK).
    Плюсы: онлайн-бэкап, поддержка инкрементальных.
    Минусы: требует дополнительного ПО.

Стратегии резервного копирования​

Выбор стратегии определяется целевыми показателями:

  • RPO (Recovery Point Objective) — максимальная допустимая потеря данных (например, 15 минут);
  • RTO (Recovery Time Objective) — максимальное допустимое время простоя (например, 1 час).

Полное (Full) — копирование всего. База для восстановления. Частота: ежедневно, еженедельно.

Инкрементное (Incremental) — копирование блоков, изменённых с момента последнего бэкапа любого типа. Требует цепочки: Full → Inc1 → Inc2 → ...
Плюсы: минимальный объём.
Минусы: восстановление требует применения всех бэкапов в цепочке — медленно, уязвимо к потере одного звена.

Дифференциальное (Differential) — копирование блоков, изменённых с момента последнего полного бэкапа.
Плюсы: восстановление быстрее (Full + Diff), менее уязвимо.
Минусы: объём Diff растёт со временем.

Горячее (Online) — бэкап без остановки СУБД. Требует включённого архивирования журналов (WAL, Archive Log, Transaction Log, Binary Log).
Холодное (Offline) — бэкап при остановленной СУБД. Проще, но требует простоя.

Настройка автоматического резервного копирования​

Ручное выполнение бэкапов недопустимо в production.

  • Системные планировщики: cron (Linux), Task Scheduler (Windows) — запускают скрипты обёртки (backup.sh, backup.ps1), которые вызывают pg_dump, mysqldump, sqlcmd -Q "BACKUP...".
  • Встроенные инструменты:
    • PostgreSQL: pgAgent (расширение);
    • Oracle: DBMS_SCHEDULER;
    • MS SQL: SQL Server Agent;
    • MySQL: Event Scheduler (редко используется для бэкапов).
  • Сторонние решения: pg_probackup, Bacula, Veeam, Barman — предоставляют мониторинг, проверку целостности, управление сроками хранения.

Ключевые требования к автоматизации:

  • Логирование результатов (успех/ошибка, объём, время);
  • Проверка целостности (например, pg_restore --list, RESTORE VERIFYONLY);
  • Ротация и удаление старых бэкапов по политике (например, 7 daily, 4 weekly, 12 monthly);
  • Хранение копий вне основного сервера (сетевое хранилище, облако, лента).

Восстановление​

Восстановление — управляемый процесс возврата системы в рабочее состояние с соблюдением требований к целостности данных и минимизации потерь. Успешное восстановление невозможно без предварительно отлаженной и проверенной стратегии резервного копирования.

Восстановление из логического бэкапа​

Логическое восстановление — импорт данных из текстовых или структурированных файлов. Подходит для переноса между средами, исправления ошибок на уровне данных (например, отката некорректного UPDATE), но не для аварийного восстановления всей системы.

  • PostgreSQL:
    Скрипт, созданный pg_dump, выполняется через psql -f dump.sql (для plain-формата) или pg_restore -d dbname archive.dump (для custom/tar-формата).
    Важно: перед восстановлением необходимо создать целевую базу и пользователя. Для полного восстановления кластера используется pg_restore -d postgres --clean --if-exists с дампом, полученным через pg_dumpall --globals-only, затем восстанавливаются отдельные БД.
    Особенность: порядок создания объектов в дампе гарантирует, что зависимости (типы, функции, таблицы) создаются корректно.

  • Oracle:
    Импорт через impdp (Data Pump Import). Требует указания каталога (DIRECTORY), имени дампа и режима (FULL=Y, SCHEMAS=APP_USER).
    Ключевой параметр: TABLE_EXISTS_ACTION=REPLACE — пересоздаёт таблицы, но при этом теряются внешние ключи, триггеры, гранты — их необходимо восстанавливать отдельно. Для точного восстановления состояния рекомендуется использовать CONTENT=ALL.

  • Microsoft SQL Server:
    RESTORE DATABASE AppDB FROM DISK = 'path.bak' — применяется только к физическим бэкапам.
    Для логического восстановления (из bcp или SSIS-файлов) используется BULK INSERT или INSERT ... SELECT FROM OPENROWSET, что требует предварительного создания структуры БД.

  • MySQL:
    mysql app_db < dump.sql — стандартный способ. Требует, чтобы целевая БД существовала (если в дампе нет CREATE DATABASE).
    При использовании mysqldump --single-transaction данные в дампе согласованы на момент начала экспорта. Для ускорения импорта отключают проверки: SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;, затем включают после завершения.

Логическое восстановление не гарантирует битовой идентичности с исходной системой (например, физическое расположение строк в файле отличается), но обеспечивает логическую целостность.

Восстановление из физического бэкапа​

Физическое восстановление — замена файлов данных на копии, полученные при бэкапе. Это единственный способ достичь полной идентичности состояния, включая физическую структуру, статистику, кэшированные планы.

  • PostgreSQL:

    1. Остановка сервера (pg_ctl stop -m fast);
    2. Замена содержимого каталога данных (обычно /var/lib/pgsql/data) на файлы из pg_basebackup или снимка;
    3. При наличии архивных WAL: настройка recovery.conf (до 12 версии) или postgresql.auto.conf (с 12+) с указанием restore_command и recovery_target_time;
    4. Запуск сервера — он автоматически применяет WAL-файлы до указанной точки.
      Критически важно: каталог pg_wal (ранее pg_xlog) должен быть пустым перед восстановлением, иначе возможна путаница с номерами сегментов.

  • Oracle:
    Процесс управляется через RMAN:

    RMAN> STARTUP MOUNT;
    RMAN> RESTORE DATABASE;
    RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('2025-11-18 14:30:00', 'YYYY-MM-DD HH24:MI:SS')";
    RMAN> ALTER DATABASE OPEN RESETLOGS;

    RESETLOGS создаёт новые журналы Redo и обнуляет номера SCN — обязательный шаг при PITR. Без архивных логов восстановление возможно только до момента создания бэкапа.

  • Microsoft SQL Server:

    RESTORE DATABASE AppDB 
    FROM DISK = 'full.bak' 
    WITH NORECOVERY;  -- не открывать БД, ожидать log backup

    RESTORE LOG AppDB 
    FROM DISK = 'log1.trn' 
    WITH NORECOVERY;

    RESTORE LOG AppDB 
    FROM DISK = 'log2.trn' 
    WITH RECOVERY, STOPAT = '2025-11-18 14:30:00';

    Ключевые опции: NORECOVERY (для цепочки), RECOVERY (финальный шаг), STOPAT (для PITR). Требует, чтобы бэкапы логов были сделаны в режиме FULL или BULK_LOGGED Recovery Model.

  • MySQL:

    1. Остановка mysqld;
    2. Замена файлов в каталоге данных (InnoDB: ibdata1, ib_logfile*, *.ibd);
    3. Запуск сервера — InnoDB автоматически выполняет crash recovery на основе ib_logfile;
    4. Для PITR: после запуска применение бинарных логов: 
      mysqlbinlog --start-datetime="2025-11-18 14:00:00" \
                  --stop-datetime="2025-11-18 14:30:00" \
                  binlog.000001 binlog.000002 | mysql -u root -p

    Требуется, чтобы binlog_format = ROW и log_bin = ON были включены до сбоя.

Восстановление на момент времени (Point-in-Time Recovery, PITR)​

PITR — ключевая технология, позволяющая минимизировать RPO до нескольких секунд. Она основана на непрерывном архивировании журналов транзакций.

Общий принцип для всех СУБД:

  1. Восстановить последний полный физический бэкап (состояние на момент T0);
  2. Последовательно применить все архивные журналы, созданные после T0, вплоть до целевого момента T1 (T0 < T1).

Без включённого архивирования журналов (WAL archive, ARCHIVELOG, Transaction Log backup, Binary Log) PITR невозможен. Журналы должны храниться отдельно от основных файлов данных — иначе их утрата при сбое диска делает PITR бессмысленным.

Типичная ошибка: включить архивирование, но не настроить archive_command (PostgreSQL), не настроить назначение архивов в RMAN (Oracle), не делать регулярные LOG BACKUP (MS SQL), не копировать бинарные логи (MySQL). В этом случае система думает, что делает PITR, но на деле может восстановиться только до момента последнего полного бэкапа.

Репликация, масштабирование и кластеризация​

Эти механизмы решают три взаимосвязанные задачи: отказоустойчивость, масштабирование и географическое распределение. Выбор архитектуры зависит от приоритетов: если критична доступность — кластеризация; если нагрузка на чтение — репликация; если нагрузка на запись — шардинг.

Зачем нужна репликация?​

Репликация — это процесс копирования данных с одного узла (источника, primary) на другие (реплики, standby/replica). Цели:

  • Отказоустойчивость: при отказе primary автоматически или вручную переключаются на реплику;
  • Масштабирование чтения: направление запросов SELECT на реплики снижает нагрузку на primary;
  • Географическое распределение: размещение реплик в разных дата-центрах уменьшает задержки для пользователей;
  • Выполнение ресурсоёмких операций: бэкапы, аналитические запросы, отчёты — на реплике, без влияния на OLTP-нагрузку.

Типы репликации​

Физическая (бинарная) репликация​

Копирование изменений на уровне журналов транзакций (WAL, Redo Log, Transaction Log, Binary Log). Реплика получает поток бинарных записей и применяет их локально.

  • PostgreSQL: Streaming Replication — встроенная, асинхронная или синхронная (с synchronous_commit = remote_write/remote_apply). Реплика работает в режиме hot standby — разрешены запросы SELECT, но не DDL/DML.
    Преимущества: низкая задержка, минимальная нагрузка на primary, высокая надёжность.
    Недостатки: реплика полностью идентична primary — нельзя реплицировать только часть БД.

  • Oracle: Data Guard — enterprise-решение для физической репликации. Поддерживает Maximum Performance (асинхронно), Maximum Availability (полусинхронно), Maximum Protection (синхронно).
    Реплика может быть в режиме Physical Standby (только для failover) или Active Data Guard (разрешены запросы чтения).

  • MS SQL: Always On Availability Groups — замена устаревшего Database Mirroring. Группа баз данных реплицируется на один или несколько вторичных узлов. Поддерживает синхронный и асинхронный коммит, автоматический failover при наличии кворума (WSFC).
    Вторичные реплики могут использоваться для чтения (READ_ONLY routing).

  • MySQL: Standard Replication (асинхронная) и Semisynchronous Replication. Основана на бинарных логах: primary записывает изменения в binlog, replica подключается через IO_THREAD, копирует логи в свой relay log, затем SQL_THREAD применяет их.
    Проблема: задержка репликации (replication lag) при высокой нагрузке на запись.

Логическая репликация​

Копирование изменений на уровне логических операций (INSERT/UPDATE/DELETE) или строк. Позволяет гибко управлять объёмом данных.

  • PostgreSQL: Logical Replication (с версии 10). Использует publication (источник) и subscription (потребитель). Можно реплицировать отдельные таблицы, фильтровать по условиям, даже объединять данные с других СУБД через расширения.
    Применение: микросервисы, data warehousing, миграции.

  • Oracle: GoldenGate — standalone-продукт для логической репликации и интеграции. Поддерживает гетерогенные системы (например, Oracle → Kafka → PostgreSQL).

  • MS SQL: Transactional Replication — публикация статей (таблиц, представлений), подписка на них. Подходит для сценариев «один ко многим», с фильтрацией по строкам/столбцам.

  • MySQL: Group Replication и InnoDB Cluster — реализуют кворум-базированную репликацию с автоматическим разрешением конфликтов (на основе векторных часов). Обеспечивают высокую доступность и согласованность (по модели primary-secondary или multi-primary).

Масштабирование​

  • Вертикальное (Scale-Up): увеличение ресурсов одного сервера (CPU, RAM, быстрые диски). Просто в реализации, но имеет физические и экономические пределы. Подходит для монолитных приложений.

  • Горизонтальное (Scale-Out): добавление узлов.

    • Масштабирование чтения: достигается через репликацию — запросы SELECT распределяются между репликами (с помощью pgpool-II, HAProxy, ProxySQL).
    • Масштабирование записи: требует шардинга — разбиения данных по ключу (например, user_id % 16) и распределения шардов по разным серверам. Управление шардингом может быть встроенным (Citus для PostgreSQL, Vitess для MySQL) или прикладным.

Кластеризация​

Кластеризация объединяет несколько узлов в единый отказоустойчивый сервис.

  • Высокая доступность (HA):

    • PostgreSQL: стек Patroni + etcd/zooKeeper/Consul — управляет лидерством, автоматически переключает primary при отказе, интегрируется с pg_rewind для быстрого восстановления бывшего primary.
    • Oracle: RAC (Real Application Clusters) — несколько узлов одновременно работают с одними и теми же файлами данных (через shared storage или ASM), обеспечивая отказоустойчивость и масштабирование нагрузки.
    • MS SQL: Always On Failover Cluster Instances (FCI) — кластер на уровне ОС (WSFC), общий диск; Availability Groups — кластер на уровне БД, без общего диска.
    • MySQL: InnoDB Cluster — интеграция Group Replication, MySQL Shell и MySQL Router для автоматического маршрутизирования запросов.

  • Балансировка нагрузки:
    Простое круговое распределение (round-robin) недостаточно — необходимо учитывать:

    • Состояние узла (здоров/нездоров);
    • Тип запроса (чтение/запись);
    • Задержку репликации (направлять чтение только на реплики с replication_lag < 5s).
      Инструменты: HAProxy (с кастомными health-checks), pgpool-II (для PostgreSQL), ProxySQL (для MySQL).

Производительность​

Оптимизация производительности — итеративный процесс: измерить → проанализировать → изменить → измерить снова. Нет универсальных «лучших настроек» — каждая система уникальна.

Общие принципы​

  • Измерение — основа решения. Без метрик (время выполнения, количество I/O, CPU usage) любые изменения — слепые.
  • Изменять по одному параметру. Комбинированные изменения делают невозможным определение причины эффекта.
  • Тестировать в окружении, приближенном к production. Нагрузочное тестирование (например, через pgbench, sysbench, HammerDB) должно имитировать реальный профиль запросов.

Ключевые области для настройки​

Память​

Неправильное распределение — главная причина проблем.

  • Буферные пулы (кэширование данных в ОЗУ):

    • PostgreSQL: shared_buffers (ядро СУБД) + ОС-кэш (остальная память); оптимально shared_buffers = 25% RAM, остальное — ОС.
    • Oracle: SGA_TARGET (разделяемая память: DB_CACHE_SIZE, SHARED_POOL_SIZE) + PGA_AGGREGATE_TARGET (приватная память процессов).
    • MS SQL: max server memory — обязательно ограничивать (например, RAM - 4 GB), чтобы оставить место ОС и другим процессам.
    • MySQL: innodb_buffer_pool_size — до 70–80% RAM при dedicated-сервере.

  • Память для временных операций:

    • PostgreSQL: work_mem — память на операцию (сортировка, хеширование); при 100 одновременных запросах с work_mem = 64MB может потребоваться 6.4 GB.
    • Oracle: SORT_AREA_SIZE (устаревший), PGA_AGGREGATE_TARGET управляет динамически.
    • MS SQL: memory grants выделяются динамически, контролируются через max server memory и Resource Governor.
    • MySQL: sort_buffer_size, join_buffer_size — на соединение, поэтому осторожность при высокой конкуренции.

Дисковая подсистема​

I/O — самый медленный компонент.

  • Тип дисков: SSD (NVMe предпочтительно) обязательны для журналов транзакций и файлов данных. HDD допустимы только для архивных данных и cold backup’ов.
  • RAID:
    • RAID 10 — оптимален для журналов (высокая скорость записи, отказоустойчивость);
    • RAID 5/6 — для файлов данных при ограниченном бюджете (но с осторожностью из-за write penalty).
  • Размещение: журналы транзакций (WAL, Redo, Transaction Log, Binary Log) обязательно на отдельном физическом диске/массиве от файлов данных — иначе запись в журнал блокируется записью данных.

Параллелизм​

Современные СУБД активно используют многопоточность.

  • PostgreSQL: max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather — управляют параллельным выполнением запросов (начиная с 9.6).
  • Oracle: PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU.
  • MS SQL: max degree of parallelism (MAXDOP) — ограничивает число ядер на запрос (часто 4–8 для OLTP).
  • MySQL: innodb_parallel_read_threads (с 8.0) для параллельного чтения.

Планировщик запросов​

Оптимизатор выбирает план выполнения на основе стоимости, рассчитанной по статистике.

  • Сбор статистики:

    • ANALYZE (PostgreSQL, MySQL), DBMS_STATS.GATHER_TABLE_STATS (Oracle), UPDATE STATISTICS (MS SQL) — должен выполняться регулярно (автоматически или по расписанию).
    • Устаревшая статистика приводит к выбору неоптимальных планов (например, full scan вместо index scan).

  • Настройка стоимости операций:

    • random_page_cost, seq_page_cost (PostgreSQL) — корректируют предпочтение index scan vs seq scan в зависимости от типа диска (для SSD random_page_cost снижается до 1.1).
    • OPTIMIZER_INDEX_COST_ADJ (Oracle), cost threshold for parallelism (MS SQL).

Сетевые настройки​

При распределённых системах сеть может стать узким местом.

  • Размеры буферов сокетов (net.core.rmem_max, net.core.wmem_max в Linux) увеличивают пропускную способность при bulk-операциях.
  • Включение tcp_keepalive помогает обнаруживать «мёртвые» соединения.

Инструменты для анализа производительности​

  • PostgreSQL:

    • EXPLAIN (ANALYZE, BUFFERS) — показывает реальное время, число блоков чтения/записи;
    • pg_stat_statements — собирает статистику по всем запросам (требует установки расширения);
    • auto_explain — логирует планы медленных запросов.

  • Oracle:

    • EXPLAIN PLAN FOR ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); — план выполнения;
    • AWR (Automatic Workload Repository) — отчёты по нагрузке за период;
    • ADDM (Automatic Database Diagnostic Monitor) — автоматический анализ AWR и рекомендации.

  • MS SQL:

    • SET STATISTICS IO, TIME ON — показывает logical/physical reads и время CPU;
    • Actual Execution Plan в SSMS — визуальный план с реальными метриками;
    • DMVs (sys.dm_exec_query_stats, sys.dm_os_wait_stats) — детальная статистика.

  • MySQL:

    • EXPLAIN FORMAT=JSON — расширенный план;
    • Performance Schema — низкоуровневая телеметрия;
    • Slow Query Log — фиксация запросов дольше long_query_time.